import configparser
from threading import Lock
from typing import List
from datetime import datetime
from mysql.connector import pooling
import calendar

import mysql.connector
import logging

lock = Lock()

class ImageDB:
    """
    用来处理拍摄设备(手机)数据
    """

    def __init__(self, db_config):
        self.config = db_config
        self.pool:pooling.MySQLConnectionPool = None

    def connect(self):
        try:
            prefix = "test_data"
            self.pool = pooling.MySQLConnectionPool(pool_name=f"{prefix}_image_pool", pool_size=8, **self.config)
            print("image_pool database connect success")
        except Exception as e:
            print(f"image_pool database connect failed: {e}")
            raise Exception("image_pool 数据库无法连接")

    def get_connection(self):
        return self.pool.get_connection()

    def get_cursor(self, conn, dictionary=None):
        return conn.cursor(dictionary=dictionary)



    def find_product(self , start_time, end_time, limit):
        """ 根据机位ID 获取最近的 棋盘格图片地址 """
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn , True)
            self.execute(cursor, f"select product_id, DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time from workshop1_l1产线_product_record where create_time > '{start_time}' and create_time < '{end_time}' LIMIT {limit} ;")
            result = cursor.fetchall()

            cursor.close()
            conn.commit()
            self.close_conn(conn)

            if result is None:
                return None
            return result

    def find_img_data(self, product_ids):
        """ 根据机位ID和图片ID, 查询已有的图片信息 """
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn, True)
            self.execute(cursor, f"select product_id, device_id, area_id, image_name, image_path, DATE_FORMAT(creat_time, '%Y-%m-%d %H:%i:%s') AS creat_time  from `imgs_l1产线_2024_12` where product_id in ({','.join(product_ids)})")
            result = cursor.fetchall()

            cursor.close()
            conn.commit()
            self.close_conn(conn)

            if result is None:
                return None
            return result


    def close_conn(self , conn):
        if conn is not None:
            conn.close()

    def close(self):
        if self.pool:
            self.pool._remove_connections()

    def execute(self, cursor, sql):
        try:
            cursor.execute(sql)
        except Exception as e:
            self.logger.error(sql)
            self.logger.exception(e)

